package com.cloud.platform.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class JDBCUtil {
	
	public static final int MYSQL = 1;
	public static final int SQLSERVER = 2;
	public static final int ORACLE = 3;
	
	public static final String DRIVER_CLASS_MYSQL = "com.mysql.jdbc.Driver";
	public static final String DRIVER_CLASS_SQLSERVER = "";
	public static final String DRIVER_CLASS_ORACLE = "";

	/**
	 * open database connection
	 * 
	 * @param ip
	 * @param port
	 * @param dbName
	 * @param username
	 * @param password
	 * @param driver
	 * @return
	 * @throws Exception 
	 */
	public static Connection openConnection(String ip, String port,
			String dbName, String username, String password, int dbType) throws Exception {
		
		// register database driver
		Class.forName(getDriver(dbType));
		
		// combine database url
		String url = combineUrl(ip, port, dbName, dbType);
		
		// get database connection
		Connection conn = DriverManager.getConnection(url, username, password);
		
		return conn;
	}
	
	/**
	 * get driver class by database type
	 * 
	 * @param dbType
	 * @return
	 */
	private static String getDriver(int dbType) {
		
		String driver = null;
		
		switch(dbType) {
		case MYSQL:
			driver = DRIVER_CLASS_MYSQL;
			break;
			
		case SQLSERVER:
			driver = DRIVER_CLASS_SQLSERVER;
			break;
			
		case ORACLE:
			driver = DRIVER_CLASS_ORACLE;
			break;
		}
		
		return driver;
	}
	
	/**
	 * combine database connect url
	 * 
	 * @param ip
	 * @param port
	 * @param dbName
	 * @param dbType
	 * @return
	 */
	private static String combineUrl(String ip, String port, String dbName, int dbType) {
		
		String url = null;
		
		switch(dbType) {
		case MYSQL:
			url = "jdbc:mysql://" + ip + ":" + port + "/" + dbName
					+ "?useUnicode=true&amp;characterEncoding=UTF-8";
			break;
			
		case SQLSERVER:
			url = "";
			break;
			
		case ORACLE:
			url = "";
			break;
		}
		
		return url;
	}
	
	/**
	 * close connection
	 * 
	 * @param conn
	 * @throws SQLException 
	 */
	public static void closeConnection(Connection conn) throws SQLException {
			conn.close();
	}
	
	/**
	 * ===================  test  ===================
	 */
	public static void main(String[] args) {
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		try {
			// open connection
			conn = openConnection("localhost", "3306", "cloud", "root",
					"yuzhengzhong", MYSQL);
			
			// excute statement
			st = conn.createStatement();
			
			st.executeUpdate("create table sql...");
			st.executeUpdate("update sql...");
			st.executeUpdate("insert sql...");
			
			rs = st.executeQuery("query sql...");
			
			// iterate result
			while(rs.next()) {
				System.out.println(rs.getInt("field"));
				System.out.println(rs.getString("field"));
				System.out.println(rs.getDate("field"));
				// .........
			}
			
			// get field number count
			ResultSetMetaData metaData = rs.getMetaData();
			int fieldCount = metaData.getColumnCount();
			
			// get record by index
			List<Object[]> result = new ArrayList();
			
			while(rs.next()) {
				Object[] record = new Object[fieldCount];
				
				for(int i = 0; i < record.length; i++) {
					record[i] = rs.getObject(i);
				}
				
				result.add(record);
			}
			
			/**
			 * prepare statement
			 * 1. set variable
			 * 2. cache statement, do prepare statement work for once
			 */
			PreparedStatement pst = conn.prepareStatement("query sql, use ? for space...");
			
			int value1 = 1000;
			pst.setInt(1, value1);
			
			String value2 = "";
			pst.setString(2, value2);
			
			// excute batch sql
			pst.addBatch("sql 1...");
			pst.addBatch("sql 2...");
			pst.executeUpdate();
			
		} catch(Exception e) {
			
		} finally {
			try {
				rs.close();
				st.close();
				closeConnection(conn);
			} catch(Exception e) {}
		}
	}
}
